<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">

    <changeSet id="001" author="alexander (bigtows) chapchuk">
        <createTable tableName="doctor">
            <column name="user_id" type="uuid">
                <constraints primaryKey="true"/>
            </column>
            <column name="full_name" type="varchar(100)"/>
            <column name="rate" type="double">
                <constraints nullable="false"/>
            </column>
            <column name="modality" type="varchar(30)"/>
            <column name="optional_modality" type="varchar(30) []"/>
        </createTable>

        <createTable tableName="absence_schedule">
            <column name="id" type="bigint">
                <constraints primaryKey="true"/>
            </column>
            <column name="doctor_id" type="uuid">
                <constraints referencedTableName="doctor" referencedColumnNames="user_id" foreignKeyName="doctor_id"
                             nullable="false"/>
            </column>
            <column name="start" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="end" type="datetime">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <createSequence sequenceName="absence_schedule_sequence"/>
    </changeSet>
    <changeSet id="002" author="alexander (bigtows) chapchuk">
        <dropColumn tableName="doctor" columnName="full_name"/>
        <addColumn tableName="doctor">
            <column name="hours" type="double">
                <constraints nullable="false"/>
            </column>
            <column name="work_days" type="int">
                <constraints nullable="false"/>
            </column>
            <column name="weekend_days" type="int">
                <constraints nullable="false"/>
            </column>
        </addColumn>
    </changeSet>

    <changeSet id="003" author="alexander (bigtows) chapchuk">
        <addColumn tableName="doctor">
            <column name="start_contract" type="date"/>
            <column name="end_contract" type="date"/>
        </addColumn>
    </changeSet>

    <changeSet id="004" author="alexander (bigtows) chapchuk">
        <createSequence sequenceName="service_number_sequence" startValue="100000"/>
        <addColumn tableName="doctor">
            <column name="service_number" type="bigint" defaultValueSequenceNext="service_number_sequence">
                <constraints nullable="false"/>
            </column>
        </addColumn>
        <sql>
            UPDATE doctor
            SET service_number = (select nextval('service_number_sequence'))
            WHERE service_number IS NULL;
        </sql>
    </changeSet>

    <changeSet id="005" author="alexander (bigtows) chapchuk">
        <modifyDataType tableName="absence_schedule" columnName="start" newDataType="date"/>
        <modifyDataType tableName="absence_schedule" columnName="end" newDataType="date"/>
        <sql>
            CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
            ALTER TABLE absence_schedule ALTER COLUMN id SET DATA TYPE UUID USING (uuid_generate_v4());
        </sql>
        <dropSequence sequenceName="absence_schedule_sequence"/>
    </changeSet>

    <changeSet id="006" author="alexander (bigtows) chapchuk">
        <dropColumn tableName="doctor" columnName="weekend_days"/>
        <dropColumn tableName="doctor" columnName="work_days"/>
        <addColumn tableName="doctor">
            <column name="work_days" type="varchar(30) []"/>
        </addColumn>
    </changeSet>
    <changeSet id="007" author="alexander (bigtows) chapchuk">
        <addColumn tableName="doctor">
            <column name="start_work_day" type="TIME WITHOUT TIME ZONE"/>
        </addColumn>
    </changeSet>

</databaseChangeLog>
